The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.
In a database system that supports both on-line transaction processing (OLTP) and on-line analytical processing (OLAP), some tables, known as “fact” tables, are set aside to store fact data, while some other tables, known as “dimension” tables, are set aside to store dimension data. The dimension data classifies values stored in the fact tables.
To load data that is generated in a large number of on-line transactions into the fact tables and dimension tables, many different types of data operations are involved. For example, database operations that generate new records may be represented by an insert type of database operation. Database operations that update existing records may be represented by an update type of database operation. Database operations that either update existing records or insert new records depending on a target table's data content may be represented by a merge type of database operation. Database operations that delete existing records may be represented by a delete type of database operation.
Under traditional approaches, data operations are represented by distinct SQL statements that must be executed sequentially. Thus, data manipulation operation in dimension tables may be performed first; a related data manipulation operation in a fact table may be performed next. Later operations, whether manipulation, query, or aggregation operations, may require retrieving keys such as surrogate keys from the dimension tables on which the earlier data manipulation operations have acted. Even though these earlier and later operations share common information such as surrogate keys, the operations involved all require their independent data accesses to the same dimension tables. Thus, a disadvantage of these traditional techniques is that they require large number of repeated related data accesses to same data objects.
Therefore, a better approach, which would better support efficient data accesses, is needed.